package dao.Impl;

import dao.StaffsDao;
import entitl.Staffs;
import util.DateUtil;
import util.DbUtil;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;


/**
 * @Author czl
 * @Date 2023-11-16
 */
public class StaffsDaoImpl implements StaffsDao {

    @Override
    public List<Staffs> selectAll() {
        String sql="select * from staffs";
        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt=null;
        List<Staffs> list =new ArrayList<>();
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);
            rst=pstmt.executeQuery();

            while (rst.next()){
                int Id = rst.getInt(1);
                String Name = rst.getString(2);
                int Age = rst.getInt(3);
                String Phone = rst.getString(4);
                String  Pos = rst.getString(5);
                LocalDateTime addTime = rst.getTimestamp(6).toLocalDateTime();
                LocalDateTime updateTime = rst.getTimestamp(7).toLocalDateTime();

                Staffs staffs=new Staffs();
                staffs.setId(Id);
                staffs.setName(Name);
                staffs.setAge(Age);
                staffs.setPhone(Phone);
                staffs.setPos(Pos);
                staffs.setAddTime(addTime);
                staffs.setUpdateTime(updateTime);

                list.add(staffs);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DbUtil.close(conn, pstmt, rst);
        }
        return list;
    }


    @Override
    public int delete(int id) {
        String sql="delete from staffs where id=?";
        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt=null;
        try {
            pstmt=conn.prepareStatement(sql);

            pstmt.setInt(1,id);

            return pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("删除数据失败！！！");
        }finally {
            DbUtil.close(conn,pstmt,null);
        }
        return 0;
    }

    @Override
    public int insert(Staffs staffs) {
        String sql="insert into staffs(name,age,phone,pos) values(?,?,?,?)";
        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt=null;
        try {
            pstmt=conn.prepareStatement(sql);

            pstmt.setString(1,staffs.getName());
            pstmt.setInt(2,staffs.getAge());
            pstmt.setString(3,staffs.getPhone());
            pstmt.setString(4,staffs.getPos());

            return pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("添加数据失败！！");
        }finally {
            DbUtil.close(conn,pstmt,null);
        }
        return 0;
    }

    @Override
    public int update(Staffs staffs) {
        String sql="update staffs set name=?,age=?,phone=?,pos=?,update_time=? where id=?";
        //  String sql="update staffs set name=?,age=?,phone=?,pos=?,add_time=?,update_time=?";
        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt=null;
        try {
            pstmt=conn.prepareStatement(sql);

            pstmt.setString(1,staffs.getName());
            pstmt.setInt(2,staffs.getAge());
            pstmt.setString(3,staffs.getPhone());
            pstmt.setString(4,staffs.getPos());
//            Timestamp activityBegin = new Timestamp(staffs.getUpdate_time().getTime());
//            pstmt.setTimestamp(5, activityBegin);

            // LocalDateTime 转换为 Timestamp
            LocalDateTime localDateTime = staffs.getUpdateTime();
            Timestamp timestamp = DateUtil.toTimestamp(localDateTime);
            pstmt.setTimestamp(5, timestamp);

            pstmt.setInt(6,staffs.getId());

            return pstmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("修改数据失败！！！");
        }finally {
            DbUtil.close(conn,pstmt,null);
        }
        return 0;
    }

    @Override
    public List<Staffs> selectByPage(int limit, int count) {
        List<Staffs> list = new ArrayList<>();

        // 第一：定义要操作数据库的SQL 语句
        String sql = "select id,name,age,phone,pos,add_time,update_time from staffs limit ?,?";

        // 第二：获取连接对象 - 建立 Java 程序与数据库之间的桥梁
        Connection conn = DbUtil.getConnection();

        PreparedStatement pstmt = null;
        ResultSet rst = null;

        try {
            // 第三：预编译 SQL 语句，并实例化 PreparedStatement 对象
            pstmt = conn.prepareStatement(sql);

            // 第四：填充参数 - 参数来自于方法参数
            // pstmt.setXxx(下标索引,参数值)
            pstmt.setInt(1, limit);
            pstmt.setInt(2, count);

            // 第五：执行SQL语句 - executeUpdate()、executeQuery()
            rst = pstmt.executeQuery();

            // 第六：对结果集中的数据进行处理 - ResultSet - List
            while (rst.next()) {
                // 1.读取当前行中各列的数据
                int id = rst.getInt(1);
                String name = rst.getString(2);
                int age = rst.getInt(3);
                String phone = rst.getString(4);
                String staPos = rst.getString(5);
                // java.sql.Timestamp -> java.time.LocalDateTime
                LocalDateTime addTime = rst.getTimestamp(6).toLocalDateTime();
                LocalDateTime updateTime = rst.getTimestamp(7).toLocalDateTime();

                // 2.实例化实体对象 - 用于封装读取到的数据
                Staffs staffs = new Staffs();

                // 3.具体数据的封装
                staffs.setId(id);
                staffs.setName(name);
                staffs.setAge(age);
                staffs.setPhone(phone);
                staffs.setPos(staPos);
                staffs.setAddTime(addTime);
                staffs.setUpdateTime(updateTime);

                // 把各行的数据添加到List集合中
                list.add(staffs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 第七：关闭相关的对象
            DbUtil.close(conn, pstmt, rst);
        }
        return list;
    }


}

